package com.firewolf.hibernate.sql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.jdbc.Work;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class MainDriver {

	private SessionFactory factory;

	private Session session;

	private Transaction transaction;
	
	@Test
	public void batch() {
		openSession();
		
		for(int i=0;i<100;i++){
			Customer cus = new Customer();
			cus.setCustomerName("孙悟空"+i);
			cus.setSex("男");
			cus.setAge(100);
			session.save(cus);
			if(i%20==0){//和配置文件中的一致
				//刷新缓存
				session.flush();
				//清空缓存，
				session.clear();
			}
		}

		closeSession();
	}
	
	//使用存储过程
	@Test
	public void procedure(){
		openSession();
		
		session.doWork(new Work() {
			
			@Override
			public void execute(Connection connection) throws SQLException {
				CallableStatement prepareCall = connection.prepareCall("{call p_insert (?,?,?)}");
				prepareCall.setString(1, "张三2");
				prepareCall.setString(2, "男");
				prepareCall.setInt(3, 35);
				prepareCall.executeUpdate();
			}
		});
		
		closeSession();
	}

	/**
	 * @Description: 命名SQL（原生SQL）
	 * @输入和输出示例： 
	 * @思路：
	 */
	@Test
	public void nativeNamedSql() {
		openSession();
		// 这里获取到的是Query，需要强转成SQLQuery
		 SQLQuery query = (SQLQuery) session.getNamedQuery("findCustomer");
		 query.addEntity(Customer.class);//需要这么样才能转成对象

		 query.setParameter(0, 1);
		List<Customer> obj = query.list();
		System.out.println(obj);
		closeSession();
	}

	/**
	 * @Description: 使用原生SQL @输入和输出示例： @思路：
	 */
	@Test
	public void nativeSql() {
		openSession();
		String sql = "select * from customer where customername=?";
		// 创建SQLQuery
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		// 没有下面这一句，不能转换成具体的对象，查出来的只是一个对象数组
		sqlQuery.addEntity(Customer.class);
		sqlQuery.setParameter(0, "孙悟空");
		List<Customer> list = sqlQuery.list();
		System.out.println(list);

		closeSession();
	}

	/**
	 * @Description: 命名SQL @输入和输出示例： @思路：
	 */
	@Test
	public void namedSql() {
		openSession();
		Query namedQuery = session.getNamedQuery("qryByIdAndName");
		namedQuery.setParameter("customerId", 1);
		namedQuery.setParameter("customerName", "孙悟空");
		List<Customer> list = namedQuery.list();
		System.out.println(list);
		closeSession();
	}

	@Test
	public void addData() {
		openSession();

		Customer cus = new Customer();
		cus.setCustomerName("孙悟空");
		cus.setSex("男");
		cus.setAge(100);

		Set<Order> orders = new HashSet<>();
		Order e = new Order("Order001");
		orders.add(e);
		Order e2 = new Order("Order002");
		orders.add(e2);
		cus.setOrders(orders);
		session.save(cus);
		session.save(e);
		session.save(e2);

		closeSession();
	}

	private void openSession() {
		session = factory.openSession();
		transaction = session.beginTransaction();
	}

	private void closeSession() {
		transaction.commit();
		session.close();
	}

	@Before
	public void init() {
		Configuration cfg = new Configuration().configure();

		ServiceRegistry registry = new ServiceRegistryBuilder().applySettings(cfg.getProperties())
				.buildServiceRegistry();
		factory = cfg.buildSessionFactory(registry);
	}

	@After
	public void destory() {
		factory.close();
	}
}
